﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Text;
using System.Configuration;
using System.Reflection;
using Cqrs.Infrastructure.Database;
using Cqrs.Infrastructure;
using System.Data;

// Taken directly from Greg Young's Simple.CQRS
namespace EventStore
{
    public class SqlServer : IEventStore, IDisposable
    {
        private readonly IEventPublisher _publisher;

        public SqlServer(IEventPublisher publisher)
        {
            _publisher = publisher;
        }

        public IEventPublisher Publisher
        {
            get
            {
                return _publisher;
            }
        }

        private void AppendEvent(SqlConnection connection, SqlTransaction transaction, EventDescriptor eventDescriptor)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("INSERT INTO Events (AggregateRootId, Data, Version, EventType)");
            sql.AppendLine("VALUES (");
            sql.AppendLine("@AggregateRootId, @Data, @Version, @EventType)");
            SqlCommand eventCmd = new SqlCommand(sql.ToString(), connection, transaction);
            eventCmd.Parameters.Add("@AggregateRootId", System.Data.SqlDbType.UniqueIdentifier).Value = eventDescriptor.AggregateId;
            eventCmd.Parameters.Add("@Data", System.Data.SqlDbType.NVarChar).Value = eventDescriptor.EventJson;
            eventCmd.Parameters.Add("@Version", System.Data.SqlDbType.Int).Value = eventDescriptor.Version;
            eventCmd.Parameters.Add("@EventType", System.Data.SqlDbType.NVarChar).Value = eventDescriptor.EventType;
            eventCmd.ExecuteNonQuery();

            sql = new StringBuilder();
            sql.AppendLine("INSERT INTO AggregateRoots");
            sql.AppendLine("SELECT @AggregateRootId, @aggregateRootType");
            sql.AppendLine("WHERE NOT EXISTS (SELECT Id FROM AggregateRoots");
            sql.AppendLine("WHERE Id = @AggregateRootId)");
            SqlCommand aggregateCmd = new SqlCommand(sql.ToString(), connection, transaction);
            aggregateCmd = new SqlCommand(sql.ToString(), connection, transaction);
            aggregateCmd.Parameters.Add("@AggregateRootId", System.Data.SqlDbType.UniqueIdentifier).Value = eventDescriptor.AggregateId;
            aggregateCmd.Parameters.Add("@aggregateRootType", System.Data.SqlDbType.NVarChar).Value = eventDescriptor.AggregateRootType;
            aggregateCmd.ExecuteNonQuery();
        }

        public int GetLatestEventVersion(Guid aggregateId)
        {
            int version = 0;
            string queryString = "SELECT Version FROM Events WHERE AggregateRootId = @aggregateId ";
            queryString = queryString + "and version = (select max(version) from Events where AggregateRootId = @aggregateId)";
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command =
                    new SqlCommand(queryString, connection);
                connection.Open();
                command.Parameters.Add("@aggregateId", System.Data.SqlDbType.UniqueIdentifier).Value = aggregateId;

                SqlDataReader reader = command.ExecuteReader();

                // Call Read before accessing data.
                while (reader.Read())
                {
                    version = int.Parse(reader["Version"].ToString());
                }

                // Call Close when done reading.
                reader.Close();
            }
            return version;
        }

        private List<EventDescriptor> GetEventDescriptorsForAggregate(Guid aggregateId, Assembly assembly)
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT AggregateRootId, Data, Version, EventType, ar.Type 'AggregateRootType'");
            sql.AppendLine("FROM Events, AggregateRoots ar WHERE ar.Id = AggregateRootId");
            sql.AppendLine("AND AggregateRootId = @aggregateRootId");
            List<EventDescriptor> events = new List<EventDescriptor>();

            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command =
                    new SqlCommand(sql.ToString(), connection);
                connection.Open();

                command.Parameters.Add("@aggregateRootId", System.Data.SqlDbType.UniqueIdentifier).Value = aggregateId;
                SqlDataReader reader = command.ExecuteReader();

                // Call Read before accessing data.
                while (reader.Read())
                {
                    Type eventType;
                    if (assembly != null)
                        eventType = assembly.GetTypes().Where(
                            p => p.AssemblyQualifiedName == reader["EventType"].ToString()).First();
                    else
                        eventType = Type.GetType(reader["EventType"].ToString());

                    // TODO - not sure if this is correct.
                    if (eventType == null)
                        eventType = typeof(Event);

                    int version = int.Parse(reader["Version"].ToString());

                    EventDescriptor @event = new EventDescriptor(
                        Guid.Parse(reader["AggregateRootId"].ToString()),
                        reader["Data"].ToString(),
                        version,
                        eventType.AssemblyQualifiedName,
                        Type.GetType(reader["AggregateRootId"].ToString()).AssemblyQualifiedName);
                    events.Add(@event);

                }

                // Call Close when done reading.
                reader.Close();
            }

            return events;
        }

        public List<Event> GetEventsForAggregate(Guid aggregateId, Assembly assembly = null)
        {
            // todo - could optimise to not retrieve other columns.
            List<Event> events = new List<Event>();
            foreach (EventDescriptor descriptor in GetEventDescriptorsForAggregate(aggregateId, assembly))
            {
                events.Add(Event.ConvertJsonToEvent(descriptor.EventJson, Type.GetType(descriptor.EventType)));
            }
            return events;
        }

        public void SaveEvents(Guid aggregateId,
            IEnumerable<Event> events, int expectedVersion, Type aggregateRootType)
        {

            int latestEventVersion = GetLatestEventVersion(aggregateId);

            if (latestEventVersion != expectedVersion && expectedVersion != -1)
            {
                throw new ConcurrencyException();
            }
            var i = expectedVersion;

            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                foreach (var @event in events)
                {
                    i++;
                    @event.Version = i;
                    AppendEvent(connection, transaction, new EventDescriptor(
                                                aggregateId, Event.ConvertEventToJson(@event),
                                            i, @event.GetType().AssemblyQualifiedName, aggregateRootType.AssemblyQualifiedName));
                    _publisher.Publish(@event);
                }
                transaction.Commit();
            }

        }

        public static SqlConnection GetConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["EventStore"].ConnectionString);
        }

        public static DataTable GetDataTable(string sql, IList<SqlParameter> parameters = null)
        {
            DataTable dt = new DataTable();
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command =
                    new SqlCommand(sql, connection);
                connection.Open();
                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                    {
                        command.Parameters.Add(param);
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(dt);
                connection.Close();
            }

            return dt;
        }


        public List<AggregateRootModel> GetAggregateRoots(Type AggregrateRootType)
        {
            List<AggregateRootModel> aggregateRoots = new List<AggregateRootModel>();
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT Id, Type FROM AggregateRoots");
            sql.AppendLine("WHERE Type = @aggregateRootType");
            SqlParameter param = new SqlParameter("@aggregateRootType", SqlDbType.NVarChar);
            param.Value = AggregrateRootType.AssemblyQualifiedName;
            DataTable dt = GetDataTable(sql.ToString(), new List<SqlParameter>() { param });
            foreach (DataRow row in dt.Rows)
            {
                aggregateRoots.Add(new AggregateRootModel(Guid.Parse(row["Id"].ToString()), row["Type"].ToString()));
            }
            return aggregateRoots;
        }

        public void Dispose()
        {
        }
    }

    public class AggregateNotFoundException : Exception
    {
        // todo
    }

    public class ConcurrencyException : Exception
    {
        // todo
    }
}